#!/usr/bin/perl
################################################################################
# BTEQ script in Perl
# Date Time    : 2011/7/20 17:33
# Table        : FID_CHAN_COLL_SVC_MON
# Script Name  : fid_chan_coll_svc_mon0400.pl
# Script       : 电子渠道综合业务月表
# Source Table :
#    			RPT_ELECCHAN_SO_ITEM_DT_MON（电子渠道业务量明细月表）
#				MID_SUBS_INFO_MON(用户月资料中间表)
#				MID_CHAN_SO_SVC_QUAN_MON
#
# Code Table   :FCT_CDE_BUSN_COUNTER_TYPE
# Code By      :liutao
# Load Strategy:Month
# Revised By   :nohup perl fid_chan_coll_svc_mon0400.pl 20110531.dir ->22.txt &
################################################################################
use strict; # Declare using Perl strict syntax

my $HOME = $ENV{"AUTO_HOME"};
unshift(@INC, "$HOME/bin");
require etl_pub;    #通用处理模块

# ----定义数据库名----
my $VIEWDB = $ENV{"AUTO_VIEW_DB"};
my $TEMPDB = $ENV{"AUTO_TEMP_DB"};
my $SDATADB = $ENV{"AUTO_SDATADB"};
my $PDATADB = $ENV{"AUTO_PDATADB"};
my $PMARTDB = $ENV{"AUTO_PMARTDB"};
my $MMARTDB = $ENV{"AUTO_MMARTDB"};
my $NMARTDB = $ENV{"AUTO_NMARTDB"};
my $KMARTDB = $ENV{"AUTO_KMARTDB"};

my $TVIEWDB;
my $USER=$ENV{"USER"};
if ($USER eq "tetl") {  $TVIEWDB="TPVIEW";  }
else {  $TVIEWDB=$ETL::PVIEW; }

# ----定义表和视图----
my $TARGETTB = "FID_CHAN_COLL_SVC_MON";

# ----定义时间参数----
if ( $#ARGV < 0 ) {
   exit(1);
}
my $CONTROL_FILE = $ARGV[0];
my $TX_DATE = substr(${CONTROL_FILE},length(${CONTROL_FILE})-12, 8);
my $TX_MON = substr(${CONTROL_FILE},length(${CONTROL_FILE})-12, 6);
my $YEAR = substr($TX_MON, 0, 4);
my $MONTH = substr($TX_MON, 4, 2);
my $FIRSTDAY = $TX_MON.'01';
my $FIRSTMON = $YEAR.'01';
my $LASTYEAR = $YEAR - 1;


open(STDERR, ">&STDOUT");

# 中间表加载
sub BTEQ_I1
{
	my ($dbh) = @_;
	my $BTEQ_CMD_I1 =<<ENDOFINPUT;

--活跃客户数
--业务受理量
--登录客户数
--交易金额
--客户渗透率

CREATE MULTISET VOLATILE TABLE VT_ELEC_SVC_KPI
(
	City_ID CHAR(4) CHARACTER SET LATIN CASESPECIFIC
	,BUSN_DEPT_ID CHAR(8) CHARACTER SET LATIN CASESPECIFIC NOT NULL
	,CUST_Brand_ID CHAR(2) CHARACTER SET LATIN CASESPECIFIC NOT NULL
	,Channel_SUB_Type_Code CHAR(5) CHARACTER SET LATIN CASESPECIFIC NOT NULL
	,SVC_Type CHAR(2) CHARACTER SET LATIN CASESPECIFIC NOT NULL   --业务类型
	,Active_CUST_QUAN	DECIMAL(18,0) NOT NULL	--活跃客户数
	,SVC_ACPT_QUAN	    DECIMAL(18,0) NOT NULL	--业务受理量
	,Swap_ATM DECIMAL(18,2) NOT NULL	--交易金额
)	PRIMARY INDEX (City_ID,BUSN_DEPT_ID,Cust_Brand_ID,Channel_Sub_Type_Code)
	ON COMMIT PRESERVE ROWS
;
.IF ERRORCODE <> 0 THEN .QUIT 12;

--活跃客户数
INSERT	INTO VT_ELEC_SVC_KPI
SELECT	City_ID
		,BUSN_DEPT_ID
		,CUST_Brand_Code AS CUST_Brand_ID
		,Channel_SUB_Type_Code
		,CASE WHEN SVC_Type_Code ='40041' THEN '01' --查询
			  WHEN SVC_Type_Code ='40040' THEN '02' --办理
			  END AS SVC_Type
		,SUM(SVC_QUAN) AS Active_CUST_QUAN
		,0 AS SVC_ACPT_QUAN
		,0.00 AS Swap_ATM
FROM	$TVIEWDB.VW_RPT_ELECCHAN_SO_ITEM_DT_MON
WHERE	CAL_Month = $TX_MON
AND 	SVC_Type_Code LIKE ANY ('40040','40041')
AND		Channel_SUB_Type_Code IN (  '10'   ----短信营业厅
									,'20'   ----掌上营业厅
									,'31'   ----10086自动
									,'32'   ----10086人工
									,'40'   ----网上营业厅
									,'50'   ----自助终端
								 )
GROUP BY 1,2,3,4,5;
.IF ERRORCODE <> 0 THEN .QUIT 12;

--业务受理量
INSERT	INTO VT_ELEC_SVC_KPI
SELECT	City_ID
		,BUSN_DEPT_ID
		,CUST_Brand_Code AS CUST_Brand_ID
		,Channel_SUB_Type_Code
		,CASE WHEN SVC_Type_Code LIKE '3%' THEN '01' --查询
			  WHEN SVC_Type_Code LIKE '1%' THEN '02' --办理
			  END AS SVC_Type
		,0 AS Active_CUST_QUAN
		,SUM(SVC_QUAN) AS SVC_ACPT_QUAN
		,0.00 AS Swap_ATM
FROM	$TVIEWDB.VW_RPT_ELECCHAN_SO_ITEM_DT_MON
WHERE	CAL_Month = $TX_MON
AND 	SVC_Type_Code LIKE ANY ('3%','1%')
AND		Channel_SUB_Type_Code IN (   '10'   ----短信营业厅
									,'20'   ----掌上营业厅
									,'31'   ----10086自动
									,'32'   ----10086人工
									,'40'   ----网上营业厅
									,'50'   ----自助终端
								 )
GROUP BY 1,2,3,4,5;
.IF ERRORCODE <> 0 THEN .QUIT 12;

--取交易金额
INSERT	INTO VT_ELEC_SVC_KPI
SELECT	a.City_ID
		,b.Parent_Channel_ID
		,a.Cust_Brand_Id
		,a.Channel_SUB_Type_Code
		,'02' AS SVC_Type  --咨询查询时该值为0
		,0 AS Active_CUST_QUAN
		,0 AS SVC_ACPT_QUAN
		,SUM(a.Swap_ATM) AS Swap_ATM
FROM	(
		SELECT	City_ID
				,Channel_ID
				,CASE	WHEN CUST_Brand_ID IN ('11','12','13') THEN '1'	---全球通
						WHEN CUST_Brand_ID IN ('31','32') THEN '2'		---动感地带
						ELSE '3' 										---神州行
						END AS Cust_Brand_Id
				,CASE	WHEN Channel_Sub_Type_Code='80101' THEN '10'   ----短信营业厅
						WHEN Channel_Sub_Type_Code='80201' THEN '20'   ----WAP营业厅
						WHEN Channel_Sub_Type_Code='80301' THEN '31'   ----10086自动
						WHEN Channel_Sub_Type_Code='80302' THEN '32'   ----10086人工
						WHEN Channel_Sub_Type_Code='80401' THEN '40'   ----门户网站
						WHEN Channel_Sub_Type_Code='80501' THEN '50'   ----自助终端(社会电子渠道归入自助终端)
						END AS Channel_SUB_Type_Code
				,SUM(CASE WHEN svc_type_code LIKE '2%' THEN Pay_AMT else 0 END)/100.00 AS Swap_ATM
		FROM	$VIEWDB.VW_MID_CHAN_SO_SVC_QUAN_MON
		WHERE	CAL_Month = $TX_MON
		AND		Channel_Sub_Type_Code IN ('80101','80201','80301','80302','80401','80501')
		GROUP BY 1,2,3,4
		) a
		LEFT JOIN $VIEWDB.VW_FCT_CDE_BUSN_COUNTER_TYPE b
		ON a.Channel_ID = b.Channel_ID
		GROUP BY 1,2,3,4,5,6,7;
.IF ERRORCODE <> 0 THEN .QUIT 12;

--取活动用户数
CREATE MULTISET VOLATILE TABLE VT_SUBS_INFO ,NO FALLBACK ,
	CHECKSUM = DEFAULT,
	LOG
(
	City_ID CHAR(4) CHARACTER SET LATIN CASESPECIFIC,
	CUST_Brand_ID CHAR(2) CHARACTER SET LATIN CASESPECIFIC,
	BUSN_DEPT_ID CHAR(8) CHARACTER SET LATIN CASESPECIFIC,
	Activity_CUST_QUAN DECIMAL(18,0) NOT NULL
)PRIMARY INDEX (City_ID,BUSN_DEPT_ID,Cust_Brand_ID)
ON COMMIT PRESERVE ROWS
;
.IF ERRORCODE <> 0 THEN .QUIT 12;

INSERT	INTO VT_SUBS_INFO
SELECT	a.City_Id
		,a.Cust_Brand_Id
		,b.Parent_Channel_ID AS BUSN_DEPT_ID
		,SUM(a.Activity_CUST_QUAN) AS Activity_CUST_QUAN
FROM	(
		SELECT	City_Id
				,CASE WHEN CUST_Brand_ID IN ('11','12','13') THEN '1'	---全球通
						WHEN CUST_Brand_ID IN ('31','32') THEN '2'		---动感地带
						ELSE '3' 										---神州行
						END AS Cust_Brand_Id
				,Channel_ID
				,COUNT(DISTINCT SUBS_ID) AS Activity_CUST_QUAN
		FROM	${VIEWDB}.VW_MID_SUBS_INFO_MON
		WHERE	CAL_MONTH=$TX_MON
		AND		SUBS_STS_Code IN ('10','11','12','13','20','30','60')
		AND		Activity_SUBS_Flag_Code='1'
		GROUP BY 1,2,3
		) a
		LEFT JOIN $VIEWDB.VW_FCT_CDE_BUSN_COUNTER_TYPE b
		ON a.Channel_ID = b.Channel_ID
GROUP BY 1,2,3;
IF ERRORCODE <> 0 THEN .QUIT 12;

--临时目标表
CREATE MULTISET VOLATILE TABLE VT_FID_CHAN_COLL_SVC_MON
(
	City_ID CHAR(4)  CHARACTER SET LATIN CASESPECIFIC NOT NULL --地市标识
	,CUST_Brand_ID CHAR(2)  CHARACTER SET LATIN CASESPECIFIC NOT NULL --品牌
	,BUSN_DEPT_ID CHAR(8)  CHARACTER SET LATIN CASESPECIFIC NOT NULL --营业部标识
	,Channel_Sub_Type_Code CHAR(5)  CHARACTER SET LATIN CASESPECIFIC NOT NULL --电子渠道类型
	,SVC_Type CHAR(2)  CHARACTER SET LATIN CASESPECIFIC NOT NULL --业务类型
	,SVC_ACPT_QUAN DECIMAL(18,0)   NOT NULL --业务受理量
	,Active_CUST_QUAN DECIMAL(18,0)   NOT NULL --活跃客户数
	,CUST_Filter_Rate DECIMAL(18,2)   NOT NULL --客户渗透率
	,Swap_ATM DECIMAL(18,2)   NOT NULL --交易金额
)   PRIMARY INDEX  (City_ID,CUST_Brand_ID,BUSN_DEPT_ID,Channel_Sub_Type_Code,SVC_Type)
	ON COMMIT PRESERVE ROWS;
.IF ERRORCODE <> 0 THEN .QUIT 12;

INSERT	INTO VT_FID_CHAN_COLL_SVC_MON
SELECT	a.City_ID
		,a.Cust_Brand_ID
		,a.BUSN_DEPT_ID
		,a.Channel_SUB_Type_Code
		,a.SVC_Type
		,SUM(COALESCE(a.SVC_ACPT_QUAN,0)) AS SVC_ACPT_QUAN
		,SUM(COALESCE(a.Active_CUST_QUAN,0)) AS Active_CUST_QUAN
		,ZEROIFNULL(100.00*SUM(a.Active_CUST_QUAN)/NULLIFZERO(SUM(b.Activity_CUST_QUAN))) AS CUST_Filter_Rate
		,SUM(COALESCE(a.Swap_ATM,0)) AS Swap_ATM
FROM	VT_ELEC_SVC_KPI a
		LEFT JOIN VT_SUBS_INFO b
		ON	a.City_ID=b.City_ID
		AND a.Cust_Brand_ID=b.Cust_Brand_ID
		AND a.BUSN_DEPT_ID=b.BUSN_DEPT_ID
GROUP BY 1,2,3,4,5;
.IF ERRORCODE <> 0 THEN .QUIT 12;

DELETE FROM $KMARTDB.$TARGETTB WHERE CAL_Month=$TX_MON;
.IF ERRORCODE <> 0 THEN .QUIT 12;

INSERT	INTO $KMARTDB.$TARGETTB
SELECT	$TX_MON
		,City_ID
		,CUST_Brand_ID
		,BUSN_DEPT_ID
		,Channel_Sub_Type_Code
		,SVC_Type
		,SVC_ACPT_QUAN
		,Active_CUST_QUAN
		,CUST_Filter_Rate
		,0 AS VIRT_Reward
		,Swap_ATM
FROM	VT_FID_CHAN_COLL_SVC_MON;
.IF ERRORCODE <> 0 THEN .QUIT 12;

--脚本填入内容（结束）
ENDOFINPUT

	return $BTEQ_CMD_I1;
}

sub main()
{
	my $dbh=ETL::DBconnect();
	my $BTEQCMD = "";
	$BTEQCMD = BTEQ_I1($dbh);
	ETL::disconnectETL($dbh);

	return ETL::ExecuteBTEQ($BTEQCMD, $TX_DATE);

}
my $ret= main();
exit($ret)

